# -*- coding: utf-8 -*-

###########################################################################
#
#   Copyright (c) 2020  Bresee, Inc. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License"
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
###########################################################################


import time
import utils.pgsql.base.pgconn as pgconn
import utils.common.initres as initres


techtbl = "tbl_techpay"

"""
record table operation
add 
delete
modify
...
"""
_tblName = "tbl_vlog_record"

_DB_SCRIPT = f"""
CREATE TABLE IF NOT EXISTS {_tblName} (
                "recordId" SERIAL PRIMARY KEY , 
                "caseId" INTEGER NOT NULL default 0,
                "MediaURL" VARCHAR(260) NOT NULL default '',
                "MediaURLPreview" VARCHAR(260) NOT NULL default '',
                "caseName" VARCHAR(260) NOT NULL default '',
                "userId" VARCHAR(260)  NOT NULL default '',
                "userName" VARCHAR(260)  NOT NULL default '',
                "operatorId" INTEGER NOT NULL default 0,
                "activityName" VARCHAR(260) NOT NULL default '',
                "deviceName" VARCHAR(260) NOT NULL default '',
                "ProjectId" VARCHAR(260) NOT NULL default '' unique, 
                "faceNum" INTEGER NOT NULL default 0,
                "ErrorMessage" VARCHAR(260) NOT NULL default '',
                "duration" INTEGER NOT NULL default 0,
                "Status" VARCHAR(32) NOT NULL default '',
                "EventTime" VARCHAR(64) NOT NULL default '',
                "DownloadNum" INTEGER NOT NULL default 0,
                "ShareNum" INTEGER NOT NULL default 0,
                "BuyNum" INTEGER NOT NULL default 0,
                "thumbImageUrl" VARCHAR(260) NOT NULL default '',  
                "createTime"  VARCHAR(64) NOT NULL default '',
                "ticketPrice" INTEGER NOT NULL default 0,
                "templateId" INTEGER NOT NULL default 0,
                "templateName" VARCHAR(64) NOT NULL default '',
                "activityId" INTEGER NOT NULL default 0
                );

CREATE INDEX IF NOT EXISTS idx_tbl_vlogrecord_createtime  ON tbl_user("createTime");
"""


class PgRecord:
    def __init__(self):
        self._pgConn = pgconn.pgMainConn

    def __del__(self):
        pass

    def createTbl(self):
        """
        create
        :return:
        """
        return self._pgConn.exec(_DB_SCRIPT)

 

    # def create_dayTbl(self):
    #     """
    #      
    #     :return:
    #     """
    #     partitionTbl = "{}_{}".format(_tblName, commfunc.utcnow_ymd())
    #     sql = """CREATE TABLE IF not exists {} PARTITION OF {} FOR VALUES FROM (\'{}\') TO (\'{}\');""".format(
    #         partitionTbl, _tblName,
    #         commfunc.utcday(),
    #         commfunc.utcndayafter(1),
    #         )
    #     return self._pgConn.exec(sql)

    def addRecord(self, dic) -> str:
        if "createTime" not in dic :
            dic["createTime"] = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        sql_key = '\"' + '\", \"'.join(dic.keys()) + '\"'
        sql_val = ''
        for key in dic:
            if type(dic[key]) is str:
                sql_val = sql_val + '\'' + dic[key] + '\','
            else:
                sql_val = sql_val + str(dic[key]) + ' ,'

  
        sql = "insert into {} ({}) values ({}) ".format(_tblName, sql_key, sql_val[:-1])
        return self._pgConn.exec(sql)


   
    def alluserId(self, userId) -> list:
        sql = f"""select a.*,n."totalFee",n."endTime" from {_tblName}  a left join {techtbl} n on a."ProjectId"=n."productId" where a."userId"='{userId}'  order by "createTime" desc LIMIT 300"""
        return self._pgConn.exec_fetchall(sql)


    def all(self, strattime, endtime) -> list:
        if strattime == "":
            sql = f"""select a.*,n."totalFee",n."endTime",n."cloud_url" from {_tblName}  a left join {techtbl} n on a."ProjectId"=n."productId"  order by "createTime" desc LIMIT 300"""
        else:
            sql = f"""select a.*,n."totalFee",n."endTime",n."cloud_url" from {_tblName}  a left join {techtbl} n on a."ProjectId"=n."productId"  where a."createTime" between '{strattime}' and '{endtime}' order by "createTime"  desc LIMIT 300"""
        return self._pgConn.exec_fetchall(sql)



    def allOperatoruserId(self, operator, userId) -> list:
        sql = f"""select a.*,n."totalFee",n."endTime" from {_tblName}   a left join {techtbl} n on a."ProjectId"=n."productId" where a."operatorId" = {operator} and a."userId" = '{userId}' order by "createTime" desc LIMIT 300"""
        return self._pgConn.exec_fetchall(sql)



    def allOperator(self, operator, strattime, endtime) -> list:
        if strattime == "":
            sql = f"""select a.*,n."totalFee",n."endTime" from {_tblName}   a left join {techtbl} n on a."ProjectId"=n."productId" where a."operatorId" = {operator} order by "createTime" desc LIMIT 300"""
        else:
            sql = f"""select a.*,n."totalFee",n."endTime" from {_tblName}   a left join {techtbl} n on a."ProjectId"=n."productId" where a."operatorId" = {operator} and a."createTime" between '{strattime}' and '{endtime}' order by "createTime" desc LIMIT 300"""
        return self._pgConn.exec_fetchall(sql)

    def edit(self, diXx, recordId):
        diXx["publishTime"] = time.strftime(
            "%Y-%m-%d %H:%M:%S", time.localtime())

        sql_key = ''
        for key in diXx:
            if type(diXx[key]) is str:
                sql_key = sql_key + '\"' + key + '\"' + ' = \'' + diXx[key] + '\','
            else:
                sql_key = sql_key + '\"' + key + '\"' + ' = ' + str(diXx[key]) + ' ,'


        sql =  "update {} set {} where  ".format(_tblName, sql_key[:-1])  + " \"recordId\" = '{}' ".format(
            recordId
        )
        return self._pgConn.exec(sql)


    def allCase(self, caseId, strattime, endtime,limit="") -> list:
        tmp = f' and a."templateName" SIMILAR TO \'%({limit})%\' ' if limit else ""
        if strattime == "":
            sql = f"""select  a.*,n."totalFee",n."endTime",n."cloud_url"  from {_tblName} a left join {techtbl} n on a."ProjectId"=n."productId"  where a."caseId" = {caseId}  {tmp} order by "createTime" desc LIMIT 300"""
        else:
            sql = f"""select  a.*,n."totalFee",n."endTime",n."cloud_url"  from {_tblName} a left join {techtbl} n on a."ProjectId"=n."productId"  where a."caseId" = {caseId} {tmp} and a."createTime" between '{strattime}' and '{endtime}' order by "createTime" desc LIMIT 300"""
        return self._pgConn.exec_fetchall(sql)


    def allCaseuserId(self, caseId, userId) -> list:
        sql = f"""select  a.*,n."totalFee",n."endTime"  from {_tblName} a left join {techtbl} n on a."ProjectId"=n."productId"  where a."caseId" = {caseId} and a."userId" = '{userId}' order by "createTime" desc LIMIT 300"""
        return self._pgConn.exec_fetchall(sql)


    def allCaseactive(self, caseId, activityName, strattime, endtime, limit="") -> list:
        tmp = f' and a."templateName" SIMILAR TO \'%({limit})%\' ' if limit else ""
        if strattime == "":
            sql = f"""select a.*,n."totalFee",n."endTime" from {_tblName} a left join {techtbl} n on a."ProjectId"=n."productId" where a."caseId" = {caseId} and a."activityName" SIMILAR TO '%({activityName})%' {tmp} order by a."createTime" desc LIMIT 300"""
        else:
            sql = f"""select a.*,n."totalFee",n."endTime" from {_tblName} a left join {techtbl} n on a."ProjectId"=n."productId" where a."caseId" = {caseId} and a."activityName" SIMILAR TO '%({activityName})%' and a."createTime" between '{strattime}' and '{endtime}' {tmp} order by a."createTime" desc LIMIT 300"""
        return self._pgConn.exec_fetchall(sql)


    def getRecordByProductId(self, productId) -> list:
        sql = f"""select * from {_tblName} where "ProjectId" = '{productId}' order by "createTime" desc LIMIT 300"""
        return self._pgConn.exec_fetchall(sql)


    def updateShareNumPlus1(self, projectId) -> bool:
        sql = 'update {} set "ShareNum"="ShareNum"+1 where "ProjectId"=\'{}\''.format(
            _tblName, projectId
        )
        res = self._pgConn.exec(sql)
        return res


    def updateBuyNumPlus1(self, projectId) -> bool:
        sql = 'update {} set "BuyNum"="BuyNum"+1 where "ProjectId"=\'{}\''.format(
            _tblName, projectId
        )
        res = self._pgConn.exec(sql)
        return res


    def updateDownloadNumPlus1(self, projectId) -> bool:
        sql = 'update {} set "DownloadNum"="DownloadNum"+1 where "ProjectId"=\'{}\''.format(
            _tblName, projectId
        )
        res = self._pgConn.exec(sql)
        return res


    def allpay(self, strattime, endtime) -> dict:
        if strattime == "":
            sql = f'''select tbl_vlog_record.* , {techtbl}."totalFee", {techtbl}."endTime",{techtbl}."cloud_url" from {_tblName} INNER JOIN  {techtbl}  on tbl_vlog_record."ProjectId"= {techtbl}."productId" order by  {techtbl}."endTime" desc LIMIT 500  '''
        else:
            sql = f'''select tbl_vlog_record.* , {techtbl}."totalFee" , {techtbl}."endTime",{techtbl}."cloud_url" from {_tblName} INNER JOIN  {techtbl}  on tbl_vlog_record."ProjectId"= {techtbl}."productId"  where   {techtbl}."endTime" between \'{strattime}\' and \'{endtime}\' order by  {techtbl}."endTime" desc LIMIT 500  '''
        return self._pgConn.exec_fetchall(sql)


    def otherpay(self, other, ids, strattime, endtime, limit="") -> dict:
        tmp = f' and tbl_vlog_record."templateName" SIMILAR TO \'%({limit})%\' ' if limit else ''
        if strattime == "":
            sql = f'''select tbl_vlog_record.* , {techtbl}."totalFee", {techtbl}."endTime",{techtbl}."cloud_url" from {_tblName} INNER JOIN  {techtbl}  on tbl_vlog_record."ProjectId"= {techtbl}."productId" where  tbl_vlog_record."{other}" = {ids} {tmp} order by  {techtbl}."endTime"  desc LIMIT 500 '''
        else:
            sql = f'''select tbl_vlog_record.* , {techtbl}."totalFee", {techtbl}."endTime",{techtbl}."cloud_url" from {_tblName} INNER JOIN  {techtbl}  on tbl_vlog_record."ProjectId"= {techtbl}."productId" where  tbl_vlog_record."{other}" = {ids} {tmp} and  {techtbl}."endTime" between \'{strattime}\' and \'{endtime}\'  order by  {techtbl}."endTime"  desc LIMIT 500 '''
        return self._pgConn.exec_fetchall(sql)


    def otherpayactive(self, other, ids, activityName, strattime, endtime, limit="") -> dict:
        tmp = f' and tbl_vlog_record."templateName" SIMILAR TO \'%({limit})%\' ' if limit else ''
        if strattime == "":
            sql = f'''select tbl_vlog_record.* , {techtbl}."totalFee", {techtbl}."endTime" from {_tblName} INNER JOIN  {techtbl}  on tbl_vlog_record."ProjectId"= {techtbl}."productId" where  tbl_vlog_record."{other}" = {ids} and tbl_vlog_record."activityName" SIMILAR TO \'%{activityName}%\' {tmp} order by  {techtbl}."endTime"  desc '''
        else:
            sql = f'''select tbl_vlog_record.* , {techtbl}."totalFee", {techtbl}."endTime" from {_tblName} INNER JOIN  {techtbl}  on tbl_vlog_record."ProjectId"= {techtbl}."productId" where  tbl_vlog_record."{other}" = {ids} and tbl_vlog_record."activityName" SIMILAR TO \'%{activityName}%\' {tmp} and  {techtbl}."endTime" between \'{strattime}\' and \'{endtime}\' order by  {techtbl}."endTime"  desc '''
            # sql = 'select a.*,n."totalFee",n."endTime" from {}  a left join  {} n on a."ProjectId"=n."productId" where  n."productId" is null  and  a."{}" = {} and a."activityName" SIMILAR TO \'%{}%\' and  a."createTime" between \'{}\' and \'{}\' {} order by "createTime" desc LIMIT 300 '.format(
            #     _tblName, techtbl, other, ids, activityName, strattime, endtime, tmp
            # )
        return self._pgConn.exec_fetchall(sql)


    def allunpay(self, strattime, endtime) -> dict:
        if strattime == "":
            sql = f'''select a.* ,n."totalFee",n."endTime" from {_tblName}  a left join  {techtbl} n on a."ProjectId"=n."productId" order by "createTime" desc LIMIT 300 '''
        else:
            sql = f'''select a.* ,n."totalFee",n."endTime" from {_tblName}  a left join  {techtbl} n on a."ProjectId"=n."productId" where a."createTime" between \'{strattime}\' and \'{endtime}\'  order by "createTime" desc LIMIT 300 '''
        return self._pgConn.exec_fetchall(sql)


    def otherunpay(self, other, ids, strattime, endtime, limit="") -> dict:
        tmp = f' and a."templateName" SIMILAR TO \'%({limit})%\' ' if limit else ''
        if strattime == "":
            sql = 'select a.*,n."totalFee",n."endTime",n."cloud_url" from {}  a left join  {} n on a."ProjectId"=n."productId" where n."productId" is null and  a."{}" = {} {} order by "createTime" desc LIMIT 300 '.format(
                _tblName, techtbl, other, ids,tmp
            )
        else:
            sql = 'select a.*,n."totalFee",n."endTime",n."cloud_url" from {}  a left join  {} n on a."ProjectId"=n."productId" where n."productId" is null  and  a."{}" = {} and  a."createTime" between \'{}\' and \'{}\'  order by "createTime" desc LIMIT 300 '.format(
                _tblName, techtbl, other, ids, strattime, endtime
            )
        return self._pgConn.exec_fetchall(sql)


    def otherunpayactive(self, other, ids, activityName, strattime, endtime, limit) -> dict:
        tmp = f' and a."templateName" SIMILAR TO \'%({limit})%\' ' if limit else ''
        if strattime == "":
            sql = 'select a.*,n."totalFee",n."endTime" from {}  a left join  {} n on a."ProjectId"=n."productId" where n."productId" is null  and  a."{}" = {} and a."activityName" SIMILAR TO \'%{}%\' {} order by "createTime" desc LIMIT 300 '.format(
                _tblName, techtbl, other, ids, activityName, tmp
            )
        else:
            sql = 'select a.*,n."totalFee",n."endTime" from {}  a left join  {} n on a."ProjectId"=n."productId" where  n."productId" is null  and  a."{}" = {} and a."activityName" SIMILAR TO \'%{}%\' and  a."createTime" between \'{}\' and \'{}\' {} order by "createTime" desc LIMIT 300 '.format(
                _tblName, techtbl, other, ids, activityName, strattime, endtime,tmp
            )
        return self._pgConn.exec_fetchall(sql)


    def casecompose(self, caseId, time, endtime, limit="") -> dict:
        tmp = f' and "templateName" SIMILAR TO \'%({limit})%\' ' if limit else ''
        sql = "select * from {} where \"caseId\" = '{}' and  \"EventTime\" between '{}' and '{}' {} ".format(
            _tblName, caseId, time, endtime, tmp
        )
        return self._pgConn.exec_fetchall(sql)


    def paycase(self, ids, time, endtime, limit="") -> dict:
        tmp = f' and "templateName" SIMILAR TO \'%({limit})%\' ' if limit else ''
        sql = "select * from  {}  where  \"caseId\" = {} and \"createTime\" between '{}' and '{}' {} ".format(
            techtbl, ids, time, endtime,tmp
        )
        return self._pgConn.exec_fetchall(sql)


    def paycashFeecase(self, ids) -> dict:
        sql = 'select * from {} where  "caseId" = {} '.format(techtbl, ids)
        return self._pgConn.exec_fetchall(sql)


    def temcompose(self, activityName, time, endtime) -> dict:
        sql = "select * from {} where \"activityName\" = '{}' and  \"EventTime\" between '{}' and '{}' ".format(
            _tblName, activityName, time, endtime
        )
        return self._pgConn.exec_fetchall(sql)

    def tempay(self, ids, time, endtime) -> dict:
        sql = f'''select tbl_vlog_record.* ,{techtbl}."totalFee" from {_tblName} INNER JOIN {techtbl}  on tbl_vlog_record."ProjectId"={techtbl}."productId" where  tbl_vlog_record."activityName" = \'{ids}\' and tbl_vlog_record."EventTime" between \'{time}\' and \'{endtime}\' '''
        return self._pgConn.exec_fetchall(sql)


    def temonecompose(self, ids, starttime, endtime) -> dict:
        # sql = 'Select a."activityName",m."templateName",m."templateId",Count(a."activityName") As Count FROM {} a left join tbl_activity n on a."activityName"=n."activityName"  left join tbl_template m on m."templateId"=n."templateId" where  a."caseId" = \'{}\' and m."caseId" = \'{}\' and a."createTime" between \'{}\' and \'{}\'  group  by a."activityName",m."templateName",m."templateId" order by Count desc '.format(
        #     _tblName, ids, ids, starttime, endtime
        # )
        sql = 'Select a."activityName",a."templateName",a."templateId",Count(a."activityName") As Count FROM {} as a where a."caseId" = \'{}\' and a."caseId" = \'{}\' and a."createTime" between \'{}\' and \'{}\'  group  by a."activityName",a."templateName",a."templateId" order by Count desc '.format(
            _tblName, ids, ids, starttime, endtime
        )
        return self._pgConn.exec_fetchall(sql)


    def registered(self,  starttime, endtime) -> dict:
        sql = 'Select *  FROM tbl_user  where "createTime" between \'{}\' and \'{}\'   '.format(
            starttime, endtime
        )
        return self._pgConn.exec_fetchall(sql)


    def temonepay(self, ids, starttime, endtime) -> dict:
        #sql = f'''select tbl_vlog_record."activityName",m."templateName",m."templateId" ,Count(tbl_vlog_record."activityName") As Count from {_tblName} INNER JOIN  {techtbl}  on tbl_vlog_record."ProjectId"= {techtbl}."productId"  left join tbl_activity n on tbl_vlog_record."activityName"=n."activityName"  left join tbl_template m on m."templateId"=n."templateId"  where  tbl_vlog_record."caseId" = \'{ids}\' and m."caseId" = \'{ids}\' and tbl_vlog_record."EventTime" between \'{starttime}\' and \'{endtime}\' group  by tbl_vlog_record."activityName",m."templateName",m."templateId" order by Count desc   '''
        sql = f'''select a."activityName",a."templateName",a."templateId",Count(a."activityName") As Count from {_tblName} as a INNER JOIN {techtbl}  on a."ProjectId"={techtbl}."productId" where a."caseId" = \'{ids}\' and a."EventTime" between \'{starttime}\' and \'{endtime}\' group by a."activityName",a."templateName",a."templateId" order by Count desc'''

        return self._pgConn.exec_fetchall(sql)

    def casebytime(self, ids, starttime, endtime) -> dict:
        sql = "select * from {} where \"caseId\" = '{}' and  \"EventTime\" between '{}' and '{}' ".format(
            _tblName, ids, starttime, endtime
        )
        return self._pgConn.exec_fetchall(sql)

    def allbytime(self,  starttime, endtime) -> dict:
        sql = "select * from {} where    \"EventTime\" between '{}' and '{}' ".format(
            _tblName,  starttime, endtime
        )
        return self._pgConn.exec_fetchall(sql)

    def get_url_faceNum(self,MediaURLPreview) -> dict:
        sql = "select distinct \"faceNum\",\"activityName\",\"templateId\",\"activityId\" from {} where \"MediaURLPreview\" = '{}'".format(
            _tblName,  MediaURLPreview)
        return self._pgConn.exec_fetchall(sql)

    def exec_sql(self,sql):
        if sql:
            return self._pgConn.exec_fetchall(sql)
        return []


    def get_grant_date(self,admin_id):
        sql = f'SELECT * from city WHERE "account_id" = {admin_id}'
        return self._pgConn.exec_fetchone(sql)['grantDate']
